"""
导出zabbix中资源使用第二个版本。
在第二个版本中，不同导出数据的功能是以定义一个新类来完成。
所有需要新的数据类都是继承History来实现。
"""
import abc
from mytool.zabbix.data_export import database
from mytool.system.date import MyDate
from mytool.office.excel import SingleExcel

db = database.DB()
table_mapping = {
    0: 'trends',        # float
    3: 'trends_uint'    # int
}
timestamp = None


def closest(mem):
    mem_value = [2, 4, 6, 8, 10, 12, 16, 24, 32, 64, 128, mem]
    mem_value = sorted(mem_value)
    i = mem_value.index(mem)
    if i + 1 == len(mem_value):
        return mem_value[i]
    if abs(mem_value[i - 1] - mem) > abs(mem_value[i + 1] - mem):
        return mem_value[i + 1]
    else:
        return mem_value[i - 1]


class Host:
    def __init__(self):
        self.host_info = """select h.name, h.host, i.ip from hosts h
                        join interface i on h.hostid = i.hostid
                        where h.hostid = {}
                        """
        self.all_hosts = """select hg.hostid, g.name as groupname, g.groupid from hosts_groups hg
                        join groups g on hg.groupid = g.groupid
                        where g.name regexp '112_CSK_whcard'"""
                        # where g.name regexp '112_CSK_whcard'"""
        self.host_status = """select status from hosts where hostid = {}"""

    def get_host(self):
        return db.query(self.all_hosts)

    def get_host_info(self, hostid):
        _host_info = db.query(self.host_info.format(hostid))
        _host_info = _host_info[0]
        if _host_info:
            return [_host_info['name'], _host_info['ip']]
        else:
            assert ValueError

    def check_host_status(self, hostid):
        """
        检查主机是启用还是禁用。
        """
        status = db.query(self.host_status.format(hostid))
        if status[0]['status'] == 0:
            return True
        else:
            return False


class History(metaclass=abc.ABCMeta):
    def __init__(self):
        # if not hasattr(self, 'sqls'):
        #     raise NotImplementedError('no sqls')
        self.sqls = []
        self.trends = """select clock, value_max from {} where itemid = {} order by clock desc limit 1"""
        self.table_name = None

    def get_history(self, hostid):
        _history = []
        item_variety = []
        for sql in self.sqls:
            itemid = db.query(sql.format(hostid))
            if not itemid:
                continue
            self.table_name = table_mapping[itemid[0]['value_type']]
            _data = db.query(self.trends.format(self.table_name, itemid[0]['itemid']))
            if not _data:
                continue
            item_variety.append(itemid)
            _history.append(_data)
        return self._data_handle(item_variety, _history)

    @abc.abstractmethod
    def _data_handle(self, item_variety, _history):
        pass

    def _round(self, _history):
        if isinstance(_history, list):
            rounded_history = []
            for per in _history:
                rounded_history.append(round(per, 2))
            return rounded_history
        else:
            return round(_history, 2)


class MemoryHistory(History):
    def __init__(self):
        super().__init__()
        self.used_mem_itemid = """select itemid, key_, value_type from items 
        where key_ regexp 'vm.memory.size.*(pav|pused)' and hostid = {}"""
        self.total_mem_itemid = """select itemid, key_, value_type from items 
        where key_ regexp 'vm.memory.size.*total' and hostid = {}"""
        self.sqls = [self.used_mem_itemid, self.total_mem_itemid]
        # self.trends = """select clock, value_max from {} where itemid = {} order by clock desc limit 1"""

    def _data_handle(self, item_variety, _history):
        mem = [0, 0]
        for n, _mem in enumerate(_history):
            key_ = item_variety[n][0]['key_']
            if 'available' in key_:
                percent = round(100 - _mem[0]['value_max'], 2)
                mem[0] = str(percent) + '%'
            elif 'used' in key_:
                percent = round(_mem[0]['value_max'], 2)
                mem[0] = str(percent) + '%'
            elif 'total' in key_:
                _mem = _mem[0]['value_max']
                _mem = _mem / 1024 / 1024 / 1024
                mem[1] = str(closest(_mem))
        return mem


class DiskHistory(History):
    def __init__(self):
        super().__init__()
        self.used_disk_itemid = """select itemid, key_, value_type from items 
        where key_ regexp 'vfs.fs.size.*,used' and hostid = {}"""
        self.total_disk_itemid = """select itemid, key_, value_type from items 
        where key_ regexp 'vfs.fs.size.*,total' and hostid = {}"""
        self.sqls = [self.used_disk_itemid, self.total_disk_itemid]
        # self.trends = """select clock, value_max from {} where itemid = {} order by clock desc limit 1"""

    def _data_handle(self, items_variety, _history):
        total = []
        for _item_variety in items_variety:
            _sum = 0
            for _item in _item_variety:
                key_ = _item['key_']
                # if len(key_.split('/')) > 2:    # 盘符超过2个以上的/就不是我们需要的监控项，直接跳过。
                #     continue
                disk = db.query(self.trends.format(self.table_name, _item['itemid']))
                if not disk:
                    continue
                disk = disk[0] if disk else disk
                if disk['clock'] > timestamp:
                    _sum += int(disk['value_max'])
            _sum = round(_sum / 1024 / 1024 / 1024, 2)
            total.append(_sum)
        return total


class CPUHistory(History):
    def __init__(self):
        super().__init__()
        self.win_cpu = """select itemid, key_, value_type from items
                        where key_ = 'system.cpu.load[all,avg1]' and hostid = {}"""
        self.linux_cpu = """select itemid, key_, value_type from items
                        where key_ like 'perf_counter%Processor%' and hostid = {}"""
        self.sqls = [self.win_cpu, self.linux_cpu]

    def _data_handle(self, item_variety, _history):
        if _history:
            return self._round(_history[0][0]['value_max'])
        else:
            return 0


class InterfaceHistory(History):
    def __init__(self):
        super().__init__()
        self.win_in_itemid = """select itemid, key_, value_type from items 
        where hostid = {} and key_ like 'perf_counter%Network%Received%'"""
        self.win_out_itemid = """select itemid, key_, value_type from items 
        where hostid = {} and key_ like 'perf_counter%Network%Sent%'"""
        self.linux_in_itemid = """select itemid, key_, value_type from items 
        where hostid = {} and key_ regexp 'net.if.in.(ens.*|eth.*)' and flags = 4"""
        self.linux_out_itemid = """select itemid, key_, value_type from items 
        where hostid = {} and key_ regexp 'net.if.out.(ens.*|eth.*)' and flags = 4"""
        self.if_total_itemid = """select itemid, key_, value_type from items 
        where hostid = {} and key_ regexp 'net.if.total.(ens.*|eth.*)' and flags = 4"""
        self.sqls = [self.win_in_itemid, self.win_out_itemid, self.linux_in_itemid, self.linux_out_itemid]

    def _data_handle(self, item_variety, _history):
        value = []
        if _history:
            for v in _history:
                value.append(v[0]['value_max'])
        return self._round(value)


def main(excel):
    cpu_hist = CPUHistory()
    mem_hist = MemoryHistory()
    disk_hist = DiskHistory()
    int_hist = InterfaceHistory()
    host = Host()
    hosts = host.get_host()

    enable_hosts = []
    disable_hosts = []
    enable_hosts.append(['业务单位', '虚拟机名', 'IP地址', 'CPU使用率', '内存使用率', '分配内存', '已使用磁盘', '分配磁盘', '网卡接收流量', '网卡发送流量'])
    for n, hostid in enumerate(hosts):
        print('total progressing: ' + str(round(n / len(hosts) * 100)) + '%', n, '/', len(hosts))
        _data = []
        _hostid = hostid['hostid']
        _data.append(hostid['groupname'])

        for info in host.get_host_info(_hostid):
            _data.append(info)
        if not host.check_host_status(_hostid):
            disable_hosts.append(_data)
            continue
        _data.append(cpu_hist.get_history(_hostid))
        for mem in mem_hist.get_history(_hostid):
            _data.append(mem)
        for disk in disk_hist.get_history(_hostid):
            _data.append(disk)
        for inter in int_hist.get_history(_hostid):
            _data.append(inter)
        enable_hosts.append(_data)

    excel.write_all_sheet(enable_hosts, '监控中的主机')
    excel.write_all_sheet(disable_hosts, '禁用中的主机')


if __name__ == '__main__':
    mydate = MyDate()
    file_date = str(mydate.month()) + '-' + str(mydate.day())
    file_name = 'export_zabbix_{}.xlsx'.format(file_date)
    path = 'f:\\'
    excel = SingleExcel(write_path=path + file_name, )
    excel.add_sheet('监控中的主机')
    excel.add_sheet('禁用中的主机')
    timestamp = mydate - '4h'
    main(excel)